/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Server.ImplementInterface;

import DAL.DBManager;
import Server.DTO.MonthlyReportDetail_DTO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author 10520_000
 */
public class MonthlyReportDetailRepository {
    public static int Insert(MonthlyReportDetail_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "INSERT INTO MONTHLYREPORTDETAIL(DetailID, ReportID, ReportingDate, TotalOfOpen, TotalOfClose) Values(?,?,?,?,?,?)";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getDetailID());
            pstmt.setString(2, info.getReportID());
            pstmt.setDate(3, (Date) info.getReportingDate());
            pstmt.setInt(4, info.getTotalOfOpen());
            pstmt.setInt(4, info.getTotalOfClose());
            iRet = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    //
    public static int Update(MonthlyReportDetail_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "UPDATE MONTHLYREPORTDETAIL SET DetailID= ?, ReportID= ?, ReportingDate= ?, TotalOfOpen= ?, TotalOfClose= ? WHERE DetailID=?";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.setString(1, info.getDetailID());
            pstmt.setString(2, info.getReportID());
            pstmt.setDate(3, (Date) info.getReportingDate());
            pstmt.setInt(4, info.getTotalOfOpen());
            pstmt.setInt(4, info.getTotalOfClose());
            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }

        return iRet;
    }

    public static int Delete(MonthlyReportDetail_DTO info) {
        int iRet = -1;
        try {
            Connection con = DBManager.getInstance().getConnection();
            String SQL = "DELETE * MONTHLYREPORTDETAIL WHERE DetailID=?";
            PreparedStatement pstmt = con.prepareStatement(SQL);
            pstmt.setString(1, info.getDetailID());

            iRet = pstmt.executeUpdate();

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return iRet;
    }

    public static void DeleteAll() {
        Connection con = DBManager.getInstance().getConnection();
        try {
            con.setAutoCommit(false);
            String SQL = "DELETE FROM MONTHLYREPORTDETAIL";
            PreparedStatement pstmt = con.prepareStatement(SQL);

            pstmt.executeUpdate();
            con.commit();
        } catch (SQLException se) {
            try {
                con.rollback();
            } catch (SQLException ise) {
            }
        } finally {
            try {
                con.setAutoCommit(true);
            } catch (SQLException fse) {
            }
        }
    }

    public static ArrayList FindAll() {
        ArrayList arr = new ArrayList();

        try {
            String sql = "SELECT * FROM MONTHLYREPORTDETAIL ORDER BY DetailID";
            Connection con = DBManager.getInstance().getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                MonthlyReportDetail_DTO info = new MonthlyReportDetail_DTO();
                info.setDetailID(rs.getString("DetailID"));
                info.setReportID(rs.getString("ReportID"));
                info.setReportingDate(rs.getDate("ReportingDate"));
                info.setTotalOfClose(rs.getInt("TotalOfClose"));
                info.setTotalOfOpen(rs.getInt("TotalOfOpen"));
                
                arr.add(info);
            }

            stmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }

    public static ArrayList FindByDetailID(String DetailID) {
        ArrayList arr = new ArrayList();

        try {
            String QRY = "SELECT * FROM MONTHLYREPORTDETAIL WHERE DetailID LIKE(?) ORDER BY DetailID";
            Connection con = DBManager.getInstance().getConnection();
            PreparedStatement pstmt = con.prepareStatement(QRY);
            pstmt.setString(1, "%" + DetailID + "%");
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                MonthlyReportDetail_DTO info = new MonthlyReportDetail_DTO();
                info.setDetailID(rs.getString("DetailID"));
                info.setReportID(rs.getString("ReportID"));
                info.setReportingDate(rs.getDate("ReportingDate"));
                info.setTotalOfClose(rs.getInt("TotalOfClose"));
                info.setTotalOfOpen(rs.getInt("TotalOfOpen"));
                arr.add(info);
            }

            pstmt.close();
        } catch (SQLException se) {
            System.out.println(se);
        }
        return arr;
    }
}
